package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DBLayer.DBConnection1;
import DBLayer.IFDBSalesOrder;
import ModelLayer.*;

public class DBSalesOrder implements IFDBSalesOrder
{
	private Connection con;
	
	public DBSalesOrder()
	{
		con = DBConnection1.getInstance().getDBcon();
	}
	
	//get all orders
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
	//find all orders for a customer
	public ArrayList<SalesOrder> getCustomerSalesOrders(int customerId, boolean retrieveAssociation)
	{
		return miscWhere("customerId='"+customerId+"'", retrieveAssociation);
	}
	
	//find order by id
	public SalesOrder getSalesOrdersByID(int orderId, boolean retrieveAssociation)
	{
		SalesOrder order = new SalesOrder();
		order = singleWhere("orderId='"+orderId+"'", retrieveAssociation);
		return order;
	}
	
	//find orders by order date
	@Override
	public ArrayList<SalesOrder> getSalesOrdersByOrderDate(String orderDate, boolean retrieveAssociation)
	{
		return miscWhere("orderDate='"+orderDate+"'", retrieveAssociation);
	}

	//find orders by delivery status
	@Override
	public ArrayList<SalesOrder> getSalesOrdersByDeliveryStatus(String deliveryStatus, boolean retrieveAssociation)
	{
		return miscWhere("deliveryStatus='"+deliveryStatus+"'", retrieveAssociation);
	}

	//find orders by delivery date
	@Override
	public ArrayList<SalesOrder> getSalesOrdersByDeliveryDate(String deliveryDate, boolean retrieveAssociation)
	{
		return miscWhere("deliveryDate='"+deliveryDate+"'", retrieveAssociation);
	}

	//find orders in invoice
	@Override
	public ArrayList<SalesOrder> getSalesOrdersInInvoice(int invoiceNo, boolean retrieveAssociation)
	{
		return miscWhere("invoiceNo='"+invoiceNo+"'", retrieveAssociation);
	}
	
	//insert
	public int insertSalesOrder(SalesOrder order) throws Exception
	{
		String query = "INSERT INTO SalesOrder(orderDate, deliveryStatus, deliveryDate, customerId, invoiceNo)" +
				" VALUES('" +
				order.getOrderDate() +"','"+
				order.getDeliveryStatus()+"','"+
				order.getDeliveryDate()+"','"+
				order.getCustomer().getID()+"','"+
				order.getInvoice().getInvoiceNo() + "')";
		
		int rc = -1;
		System.out.println("insert: " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("SalesOrder is not inserted");
	        throw new Exception ("SalesOrder is not inserted correctly!");
		}
		
		return rc;
	}

	//update
	@Override
	public int updateSalesOrder(SalesOrder order)
	{
		SalesOrder salesOrder = order;
		int rc = -1;
		
		String query = "UPDATE SalesOrder SET " +
				"orderDate='" + salesOrder.getOrderDate() +"', "+
				"deliveryStatus='"+salesOrder.getDeliveryStatus()+"', "+
				"deliveryDate='"+salesOrder.getDeliveryDate()+"', "+
				"customerId='"+salesOrder.getCustomer().getID()+"', "+
				"invoiceNo='"+salesOrder.getInvoice().getInvoiceNo()+"' " +
						"WHERE orderId='" + salesOrder.getId()+"'";
		System.out.println("Update query: " + query);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc=stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in SalesOrder: " + e);
		}
		
		return rc;
	}

	//delete
	@Override
	public int deleteSalesOrder(int orderId)
	{
		int rc=-1;
		  
	  	String query="DELETE FROM SalesOrder WHERE orderId = '" +
				orderId + "'";
                System.out.println(query);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in salesorder db: "+ex);
   	        }
		return(rc);
	}
	
    //michWere is used whenever we want to select more than one employee
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<>();
		
		String query=buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				SalesOrder order = new SalesOrder();
				order = buildSO(results);
				list.add(order);
			}
			stmt.close();
			if(retrieveAssociation)
			{
				for(SalesOrder order : list)
				{
					//Make association with the Customer
					IFDBCustomer dbcustomerObj = new DBCustomer();
					Customer customerObj = dbcustomerObj.getCustomerByID(order.getCustomer().getID(), false); 
					System.out.println("Customer is found!");
					order.setCustomer(customerObj);
					//Make association with the Invoice
					IFDBInvoice dbinvoiceObj = new DBInvoice();
					Invoice invoiceObj = dbinvoiceObj.getInvoiceByNo(order.getInvoice().getInvoiceNo(), false);
					System.out.println("Invoice is found!");
					order.setInvoice(invoiceObj);
				}
			}
		} 
		catch (Exception e)
		{
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	//SingleWhere is used when we only select one employee 	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder order = new SalesOrder();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				order=buildSO(results);
				stmt.close();
				if (retrieveAssociation)
				{
					//Make association with the Customer
					IFDBCustomer dbcustomerObj = new DBCustomer();
					Customer customerObj = dbcustomerObj.getCustomerByID(order.getCustomer().getID(), false); 
					System.out.println("Customer is found!");
					order.setCustomer(customerObj);
					//Make association with the Invoice
					IFDBInvoice dbinvoiceObj = new DBInvoice();
					Invoice invoiceObj = dbinvoiceObj.getInvoiceByNo(order.getInvoice().getInvoiceNo(), false);
					System.out.println("Invoice is found!");
					order.setInvoice(invoiceObj);
				}
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: "+e);
		}
		return order;
	}
	
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT orderId, orderDate, deliveryStatus, deliveryDate, customerId, invoiceNo FROM SalesOrder";
		if (wClause.length()>0)
		{
			query = query + " WHERE " + wClause;
		}
		return query;
	}
	
	//method to build the object
	private SalesOrder buildSO(ResultSet results)
	{
		SalesOrder order = new SalesOrder();
		try
		{
			order.setId(results.getInt("orderId"));
			order.setOrderDate(results.getString("orderDate"));
			order.setDeliveryDate(results.getString("deliveryDate"));
			order.setDeliveryStatus(results.getString("deliveryStatus"));
			order.setCustomer(new Customer(results.getInt("customerId")));
			order.setInvoice(new Invoice(results.getInt("invoiceNo")));
		}
		catch (Exception e)
		{
			System.out.println("error in building the SalesOrder object");
		}
		return order;
	}
}
